﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Windows;
using System.Windows.Forms;
using OfficeOpenXml;

using MessageBox = System.Windows.MessageBox;

namespace GPVC
{
    class ExcelIO
    {
        public struct ExcelInfo
        {
            public string fileName;
            public int countWorkSheet;
            public List<Tuple<string, int[]>> workSheetInfo;
            public List<string> defaultWorkSheetField;
            public int errInfo;
        }

        public ExcelInfo GetExcelFileInfo(string filePath)
        {
            ExcelInfo excelInfo = new ExcelInfo();
            int[] counRowCol = { 0, 0 };
            //
            using (var excel = new ExcelPackage(new FileInfo(filePath)))
            {
                var countWorkSheet = excel.Workbook.Worksheets.Count;
                //
                excelInfo.fileName = filePath;
                excelInfo.countWorkSheet = excel.Workbook.Worksheets.Count;
                excelInfo.workSheetInfo = new List<Tuple<string, int[]>>();
                //
                for (var i = 0; i < countWorkSheet; i++)
                {
                    var tmpWorkSheet = excel.Workbook.Worksheets[i];
                    var tmpWorkSheetName = tmpWorkSheet.Name;
                    if (tmpWorkSheet.Dimension == null)
                    {
                        Tuple<string, int[]> workSheetInfo = new Tuple<string, int[]>(tmpWorkSheetName, counRowCol);
                        excelInfo.workSheetInfo.Add(workSheetInfo);
                    }
                    else
                    {
                        counRowCol[0] = tmpWorkSheet.Dimension.End.Row;
                        counRowCol[1] = tmpWorkSheet.Dimension.End.Column;
                        Tuple<string, int[]> workSheetInfo = new Tuple<string, int[]>(tmpWorkSheetName, counRowCol);
                        excelInfo.workSheetInfo.Add(workSheetInfo);
                    }
                }
                excelInfo.defaultWorkSheetField = ReadExcelRow(excel, excelInfo.workSheetInfo[0].Item1, 0);

                return excelInfo;
            }
        }

        public List<string> ReadExcelRow(string filePath, string workSheetName, int row)
        {
            //
            var excelData = new List<string>();

            //
            using (var excel = new ExcelPackage(new FileInfo(filePath)))
            {
                for (var k = 0; k < excel.Workbook.Worksheets.Count; k++)
                {
                    var tmpWorkSheet = excel.Workbook.Worksheets[k];
                    var tmpWorkSheetName = tmpWorkSheet.Name;
                    if (tmpWorkSheetName == workSheetName)
                    {
                        var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                        var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                        if (row > tmpWorkSheetMaxRow)
                        {
                            MessageBox.Show("Row is out of working sheet!)",
                                            "Error", MessageBoxButton.OK);
                        }
                        else
                        {
                            for (var j = 1; j <= tmpWorkSheetMaxCol; j++)
                            {
                                excelData.Add(tmpWorkSheet.Cells[1, j].Value.ToString());
                            }
                        }
                        break;
                    }
                }
            }

            return excelData;
        }

        public List<string> ReadExcelRow(ExcelPackage excelPackage, string workSheetName, int row)
        {
            //
            var excelData = new List<string>();
            //
            for (var k = 0; k < excelPackage.Workbook.Worksheets.Count; k++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[k];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (row > tmpWorkSheetMaxRow)
                    {
                        MessageBox.Show("Row is out of working sheet!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 1; j <= tmpWorkSheetMaxCol; j++)
                        {
                            excelData.Add(tmpWorkSheet.Cells[1, j].Value.ToString());
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public int QueryFieldIndex(string filePath, string workSheetName, string field)
        {
            var rowData = ReadExcelRow(filePath, workSheetName, 0);
            var fieldIndex = rowData.IndexOf(field);
            //
            return fieldIndex + 1;
        }

        public int QueryFieldIndex(ExcelPackage excelPackage, string workSheetName, string field)
        {
            var rowData = ReadExcelRow(excelPackage, workSheetName, 0);
            var fieldIndex = rowData.IndexOf(field);
            //
            return fieldIndex + 1;
        }

        public List<List<double>> ReadExcel(string filePath, string workSheetName, int xIndex, int yIndex)
        {
            //
            List<List<double>> excelData = new List<List<double>>();

            using(var excel = new ExcelPackage(new FileInfo(filePath)))
            {
                for (var i = 0; i < excel.Workbook.Worksheets.Count; i++)
                {
                    var tmpWorkSheet = excel.Workbook.Worksheets[i];
                    var tmpWorkSheetName = tmpWorkSheet.Name;
                    if (tmpWorkSheetName == workSheetName)
                    {
                        var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                        var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                        if (xIndex > tmpWorkSheetMaxCol || yIndex > tmpWorkSheetMaxCol)
                        {
                            MessageBox.Show("Here missing some columns in excel file!)",
                                            "Error", MessageBoxButton.OK);
                        }
                        else
                        {
                            for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                            {
                                List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yIndex].Value)
                            };
                                excelData.Add(tmpData);
                            }
                        }
                        break;
                    }
                }
            }

            return excelData;
        }

        public List<List<double>> ReadExcel(string filePath, string workSheetName, string xField, string yField)
        {
            //
            var excelData = new List<List<double>>();

            //
            using (var excel = new ExcelPackage(new FileInfo(filePath)))
            {
                var xFieldIndex = QueryFieldIndex(excel, workSheetName, xField);
                var yFieldIndex = QueryFieldIndex(excel, workSheetName, yField);
                //
                for (var i = 0; i < excel.Workbook.Worksheets.Count; i++)
                {
                    var tmpWorkSheet = excel.Workbook.Worksheets[i];
                    var tmpWorkSheetName = tmpWorkSheet.Name;
                    if (tmpWorkSheetName == workSheetName)
                    {
                        var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                        var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                        if (xFieldIndex > tmpWorkSheetMaxCol || yFieldIndex > tmpWorkSheetMaxCol)
                        {
                            MessageBox.Show("Here missing some columns in excel file!)",
                                            "Error", MessageBoxButton.OK);
                        }
                        else
                        {
                            for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                            {
                                List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xFieldIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yFieldIndex].Value)
                            };
                                excelData.Add(tmpData);
                            }
                        }
                        break;
                    }
                }
            }

            return excelData;
        }

        public List<List<double>> ReadExcel(ExcelPackage excelPackage, string workSheetName, int xIndex, int yIndex)
        {
            List<List<double>> excelData = new List<List<double>>();
            //
            for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (xIndex > tmpWorkSheetMaxCol || yIndex > tmpWorkSheetMaxCol)
                    {
                        MessageBox.Show("Here missing some columns in excel file!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                        {
                            List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yIndex].Value)
                            };
                            excelData.Add(tmpData);
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public List<List<double>> ReadExcel(ExcelPackage excelPackage, string workSheetName, string xField, string yField)
        {
            List<List<double>> excelData = new List<List<double>>();
            //
            var xFieldIndex = QueryFieldIndex(excelPackage, workSheetName, xField);
            var yFieldIndex = QueryFieldIndex(excelPackage, workSheetName, yField);
            //
            for (var i = 0; i < excelPackage.Workbook.Worksheets.Count; i++)
            {
                var tmpWorkSheet = excelPackage.Workbook.Worksheets[i];
                var tmpWorkSheetName = tmpWorkSheet.Name;
                if (tmpWorkSheetName == workSheetName)
                {
                    var tmpWorkSheetMaxRow = tmpWorkSheet.Dimension.End.Row;
                    var tmpWorkSheetMaxCol = tmpWorkSheet.Dimension.End.Column;
                    if (xFieldIndex > tmpWorkSheetMaxCol || yFieldIndex > tmpWorkSheetMaxCol)
                    {
                        MessageBox.Show("Here missing some columns in excel file!)",
                                        "Error", MessageBoxButton.OK);
                    }
                    else
                    {
                        for (var j = 2; j <= tmpWorkSheetMaxRow; j++)
                        {
                            List<double> tmpData = new List<double>
                            {
                                Convert.ToDouble(tmpWorkSheet.Cells[j, 1].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, xFieldIndex].Value),
                                Convert.ToDouble(tmpWorkSheet.Cells[j, yFieldIndex].Value)
                            };
                            excelData.Add(tmpData);
                        }
                    }
                    break;
                }
            }
            return excelData;
        }

        public bool WriteExcel(string filePath, List<List<double>> outData, string workSheetName)
        {
            if (File.Exists(filePath))
            {
                var result = MessageBox.Show("文件已存在，是否覆盖当前文件？", "询问", MessageBoxButton.OKCancel);
                if (result == MessageBoxResult.OK)
                {
                    File.Delete(filePath);
                }
                else
                {
                    return false;
                }
            }

            //
            using (var excel = new ExcelPackage(new FileInfo(filePath)))
            {
                //for(var i = 0; i < excel.Workbook.Worksheets.Count; i++)
                //{
                //    // 一直未测试成功，因此采用上面的方法，
                //    // 即先删除文件，再创建一个文件名相同的文件的方式
                //    excel.Workbook.Worksheets.Delete(i);
                //}
                var ws = excel.Workbook.Worksheets.Add(workSheetName);
                //
                ws.Cells[1, 1].Value = "ID";
                ws.Cells[1, 2].Value = "Lng";
                ws.Cells[1, 3].Value = "Lat";

                //
                if (outData.Count > 0) 
                {
                    for (var i = 1; i <= outData[0].Count - 3; i++)
                    {
                        ws.Cells[1, i + 3].Value = "Band" + i.ToString();
                    }
                    //
                    var j = 2;
                    foreach (List<double> lineData in outData)
                    {
                        for (var k = 1; k <= lineData.Count; k++)
                        {
                            ws.Cells[j, k].Value = lineData[k - 1];
                        }
                        j++;
                    }
                    try
                    {
                        excel.SaveAs(new FileInfo(filePath));
                        return true;
                    }
                    catch (Exception errInfo)
                    {
                        MessageBox.Show(errInfo.Message, "Error", MessageBoxButton.OK);
                        return false;
                    }
                }
                else
                {
                    return false;
                }
            }
        }


    }
}
